<?php

class SpendingClass
{
	private $_dbTbl = 'a1901228_AxditDB.spendingTbl';
	private $_dbServer = 16;
	
	public function addSpending($spending, $explain, $date = false)
	{
		if(mEmpty($spending, $explain))
		{
			return false;
		}
		$this->dealMoney($spending);
		$this->dealDate($date);
		$explain .= "\n".date('Y-m-d H:i:s');
		db_query_sql("LOCK TABLE {$this->_dbTbl} WRITE", $this->_dbServer);
		$overage = $this->getOverage() + $spending;
		$sql = "INSERT INTO {$this->_dbTbl} (overage, spending, `explain`, `date`)VALUES({$overage}, '{$spending}', '{!{$explain}!}', '{$date}')";
		db_query_sql($sql, $this->_dbServer);
		$rs = db_get_insert_id();
		db_query_sql("UNLOCK TABLES", $this->_dbServer);
		return $rs;
	}
	
	public function getList($beginDate, $endDate, $getCount = false, $status = '', $orderBy = '`date` ASC', $limit = '0,10')
	{
		$this->dealDate($beginDate);
		$this->dealDate($endDate);
		$sql = "SELECT {field} FROM {$this->_dbTbl} WHERE `date` BETWEEN '{$beginDate}' AND '{$endDate}'";
		$sql .= in_array($status, array('y', 'n')) ? " AND `status`='{$status}'" : '';
		$sql .= " ORDER BY {$orderBy}";
		$sql .= empty($limit) ? '' : " LIMIT {$limit}";
		$sql = str_replace('{field}', $getCount ? 'COUNT(*) c' : '*', $sql) ;
		$___rs = db_query_sql($sql, $this->_dbServer);
		return $getCount ? $___rs[0]['c'] : $___rs;
	}
	
	public function cancelSpending($id, $explain)
	{
		if(mEmpty($id, $explain))
		{
			return false;
		}
		$id = intval($id);
		$date = date('Y-m-d H:i:s');
		db_query_sql("LOCK TABLE {$this->_dbTbl} WRITE", $this->_dbServer);
		$sql = "SELECT ABS(spending) spending FROM {$this->_dbTbl} WHERE ID={$id}";
		$rs = db_query_sql($sql, $this->_dbServer);
		$sql = "UPDATE {$this->_dbTbl} SET `status`='n', `explain`=CONCAT('Origin: \n', `explain`, '\ncancel: \n  {!{$explain}!}\ncancelDate: {$date}') WHERE ID='{$id}' AND `status`='y'";
		db_query_sql($sql, $this->_dbServer);
		if(db_get_affected_rows())
		{
			$sql = "UPDATE {$this->_dbTbl} SET overage=overage+{$rs[0]['spending']} WHERE ID>{$id} AND `status`='y'";
			db_query_sql($sql, $this->_dbServer);
		}
		db_query_sql("UNLOCK TABLES", $this->_dbServer);
		return 1;
	}
	
	public function getOverage()
	{
		$sql = "SELECT overage FROM {$this->_dbTbl} WHERE `status`='y' ORDER BY ID DESC LIMIT 0,1";
		$rs = db_query_sql($sql, $this->_dbServer);
		return $rs[0]['overage'];
	}
	
	public function compLastMonth()
	{
		$time = time();
		$currMonthfirstDay = date('Y-m-01', $time);
		$previousMonthFirstday = date('Y-m-d', strtotime('-1 month '.$currMonthfirstDay));
		$currDay = date('Y-m-d', $time);
		$previousMonthToday = date('Y-m-d', strtotime('-1 month', $time));
		$sql = "SELECT SUM(spending) spending FROM {$this->_dbTbl} WHERE `date` BETWEEN '{$previousMonthFirstday}' AND '{$previousMonthToday}' AND spending < 0 AND `status`='y'";
		$lastMonthRs1 = db_query_sql($sql, $this->_dbServer);
		$sql = "SELECT SUM(spending) spending FROM {$this->_dbTbl} WHERE `date` BETWEEN '{$currMonthfirstDay}' AND '{$currDay}' AND spending < 0 AND `status`='y'";
		$lastMonthRs2 = db_query_sql($sql, $this->_dbServer);
		return array('previousDate'=>"{$previousMonthFirstday}=>{$previousMonthToday}", 'previousSpending'=>$lastMonthRs1[0]['spending'], 'currDate'=>"{$currMonthfirstDay}=>{$currDay}", 'currSpending'=>$lastMonthRs2[0]['spending']);
	}
	
	public function getMonthComp($month = 24)
	{
		$time = time();
		$endDate = date('Y-m-d', $time);
		$startDate = date('Y-m-d', strtotime("-{$month} month", $time));
		$sql = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`date`), '%Y-%m') `month`, SUM(spending) spending FROM {$this->_dbTbl}
				WHERE spending < 0 AND `date` BETWEEN '{$startDate}' AND '{$endDate}  AND `status`='y''
				GROUP BY `month`
				ORDER BY `date` ASC
				";
		return db_query_sql($sql, $this->_dbServer);
	}
	
	private function dealDate(&$date)
	{
		if(empty($date) || !preg_match('/\d{4}-\d{2}-\d{2}/', $date))
		{
			$date = date('Y-m-d');
		}
	}
	
	private function dealMoney(&$money)
	{
		$money = round($money, 2);
	}
	
}
